package com.nsn.vs.sdm.util.oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

/**
 * Utility class for Oracle Database operations
 * 
 * @author Jeccy.Zhao
 *
 */
public class UtilOracle
{
	/**
	 * The LOGGER
	 */
	private static final Logger LOGGER = Logger.getLogger(UtilOracle.class);
	
	/**
	 * Default SID value for OSS
	 */
	public static final String DEFAULT_SID = "OSS"; 
	
	/**
	 * Default port
	 */
	public static final Integer DEFAULT_PORT = 1521;
	
	/**
	 * Database host
	 */
	private String host;
	
	/**
	 * User name for authentication
	 */
	private String userName;
	
	/**
	 * Password for authentication 
	 */
	private String password;
	
	/**
	 * SID
	 */
	private String sid = DEFAULT_SID;
	
	/**
	 * Port number
	 */
	private Integer port;
	
	/**
	 * Instance of database connection
	 */
	private Connection conn = null;
	
	/**
	 * Flag that indicates database connected
	 */
	private boolean isConnected = false;
	
	/**
	 * The sole constructor
	 * 
	 * @param host
	 * @param userName
	 * @param password
	 * @throws ClassNotFoundException 
	 */
	public UtilOracle (String host, String userName, 
			String password) throws ClassNotFoundException
	{
		this(host, userName, password, DEFAULT_PORT);
	}
	
	/**
	 * The sole constructor
	 * 
	 * @param host
	 * @param userName
	 * @param password
	 * @param port
	 * @throws ClassNotFoundException 
	 */
	public UtilOracle (String host, String userName, 
			String password, Integer port) throws ClassNotFoundException
	{
		this(host, userName, password, port, DEFAULT_SID);
	}
	
	/**
	 * The sole constructor
	 * 
	 * @param host
	 * @param userName
	 * @param password
	 * @param port
	 * @param sid
	 * @throws ClassNotFoundException 
	 */
	public UtilOracle (String host, String userName, 
			String password, Integer port, String sid) throws ClassNotFoundException
	{
		this.host = host;
		this.userName = userName;
		this.password = password;
		this.sid = sid;
		this.port = port;
		
		// initialization
		init();
	}
	
	/**
	 * Initialization
	 * @throws ClassNotFoundException 
	 */
	private void init() throws ClassNotFoundException
	{
		Class.forName("oracle.jdbc.driver.OracleDriver");
	}
	
	/**
	 * Connects to database
	 * 
	 * @throws SQLException
	 */
	public void connect() throws SQLException
	{
		String url = "jdbc:oracle:thin:@" + this.host + ":" + this.port + ":" + this.sid;
		conn = DriverManager.getConnection(url, this.userName, this.password);
		LOGGER.info("Connected to database: " + url);
		
		this.isConnected = true;
	}
	
	/**
	 * Queries regarding the specified SQL and returns row data
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery (String sql) throws SQLException
	{
		LOGGER.info("Executing query: " + sql);
		PreparedStatement psta = conn.prepareStatement(sql);
		return psta.executeQuery();
	}
	
	/**
	 * Disconnects with database
	 * 
	 * @throws SQLException
	 */
	public void disConnect() throws SQLException
	{
		if (conn != null)
		{
			conn.close();
			LOGGER.info("Disconnected with database.");
			
			this.isConnected = false;
		}
	}
	
	public String getUserName()
	{
		return userName;
	}

	public void setUserName(String userName)
	{
		this.userName = userName;
	}

	public String getPassword()
	{
		return password;
	}

	public void setPassword(String password)
	{
		this.password = password;
	}

	public String getSid()
	{
		return sid;
	}

	public void setSid(String sid)
	{
		this.sid = sid;
	}

	public Connection getConn()
	{
		return conn;
	}

	public void setPort(Integer port)
	{
		this.port = port;
	}

	public Integer getPort()
	{
		return port;
	}

	public void setHost(String host)
	{
		this.host = host;
	}

	public String getHost()
	{
		return host;
	}

	public void setConnected(boolean isConnected)
	{
		this.isConnected = isConnected;
	}

	public boolean isConnected()
	{
		return isConnected;
	}
	
}
